﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
///db_course 的摘要说明
/// </summary>
public class db_course
{
    SqlDBClass db;
	public db_course()
	{
        db = new SqlDBClass();
	}

    /// <summary>
    /// 查询所有 课程
    /// </summary>
    /// <returns></returns>
    public DataSet selectCourseALL() {
        //course
        string strsql = "select c.CourseID, c.CourseHour,c.about,t.teacherName teacherid,c.gold,ct.typename coursetype,(c.about + '(' + t.teacherName + ')') as ddlAbout,c.CourseName from course c,courseType ct,teacher t where c.CourseType=ct.TypeId and c.teacherid =t.teacherid and c.status = '0' and t.status = '0' order by c.updateTime ";
        return db.execSql(strsql);
    }
    
    /// <summary>
    /// 搜索课程
    /// </summary>
    /// <param name="courseId"></param>
    /// <param name="courseType"></param>
    /// <param name="courseName"></param>
    /// <param name="teacherId"></param>
    /// <returns></returns>
    public DataSet selectCourse_search(string courseId,string courseType,string courseName,string teacherId) {
        //course
        string strsql = "select c.CourseID, c.CourseHour,c.about,t.teacherName teacherid,c.gold,ct.typename coursetype ,c.CourseName from course c,courseType ct,teacher t where c.CourseType=ct.TypeId and c.teacherid =t.teacherid and c.status = '0' and t.status = '0'";
        strsql+="and c.CourseID like '%"+courseId+"%' " ;
        strsql+="and c.CourseName like '%"+courseName+"%' " ;
        if(!courseType.Equals(""))
        {
         strsql+="and c.courseType='"+courseType+"'" ;        
        }
        if(!teacherId.Equals(""))
        {
         strsql+="and c.teacherId='"+teacherId+"'" ;        
        }
        strsql += " order by convert(int,c.CourseID) desc";
        return db.execSql(strsql);
    }
    
    /// <summary>
    /// 查询单个 课程
    /// </summary>
    /// <param name="courseId"></param>
    /// <returns></returns>
    public DataSet selectCourseONE(string courseId)
    { //course 
        string strsql = "select * from course where courseId=" + courseId; 
        return db.execSql(strsql);
    }

    public DataSet selectCourseInfo_id(string courseID)
    {
        string strsql = "select c.CourseID, c.CourseHour,c.about,t.teacherName teacherid,c.gold,ct.typename coursetype ,c.CourseName,c.status,c.updatetime from course c,courseType ct,teacher t where c.CourseType=ct.TypeId and c.teacherid =t.teacherid and c.courseID='"+courseID+"'";
        return db.execSql(strsql);
    }

    public DataSet selectCuorseTopKind(string num, string type)
    {
        string strsql = "select top " + num + " * from course where coursetype=" + type + " and status=0 order by updateTime desc";
        return db.execSql(strsql);
    }

    public DataSet selectCourseAllKind(string type)
    {
        string strsql = "select * from course where coursetype=" + type + " and status=0 order by updateTime desc";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 删除课程
    /// </summary>
    /// <param name="courseId"></param>
    /// <returns></returns>
    public Boolean delCourse(string courseId)
    {  
        string strsql = "update course set status = '1' where courseId=" + courseId;
        return db.execSqlNoQuery(strsql);
    }

    /// <summary>
    /// 增加课程
    /// </summary>
    /// <param name="courseType"></param>
    /// <param name="courseHour"></param>
    /// <param name="about"></param>
    /// <param name="teacherId"></param>
    /// <param name="gold"></param>
    /// <returns></returns>
    public Boolean addCourse(string courseId,string courseType,string courseHour,string about,string teacherId,string gold,string courseName) {
        string strsql = "Insert Into course(courseId,courseType,courseHour,about,teacherId,gold,status,courseName,updateTime) Values('" + courseId + "','" + courseType + "','" + courseHour + "','" + about + "','" + teacherId + "','" + gold + "','0','" + courseName + "',getdate())";
        return db.execSqlNoQuery(strsql);
    }

    /// <summary>
    /// 更新课程
    /// </summary>
    /// <param name="courseId"></param>
    /// <param name="courseType"></param>
    /// <param name="about"></param>
    /// <param name="courseHour"></param>
    /// <param name="teacherId"></param>
    /// <param name="gold"></param>
    /// <returns></returns>
    public Boolean updateCourse(string courseId, string courseType, string about, string courseHour, string teacherId,string gold,string courseName)
    {
        string strsql = "update course set courseType='" + courseType + "',about='" + about + "',courseHour='" + courseHour + "',teacherId='"+teacherId + "',gold='"+gold+"',courseName='"+courseName+"' where courseId=" + courseId;
        return db.execSqlNoQuery(strsql);
    }
    
    
    /// <summary>
    /// 生产最新的课程ID
    /// </summary>
    /// <returns></returns>
    public string getNewCourseId()
    {
        string strsql = "select  coalesce(nullif(max(convert(int,CourseID)),''),'0')+1 as courseId from course";
    		DataSet ds = new DataSet();
			string courseId = "";
			ds = db.execSql(strsql);
				
			if (ds.Tables.Count != 0)
			{
				courseId = ds.Tables[0].Rows[0]["courseId"].ToString();
			}
				
			return courseId;
    }
    
    /// <summary>
    /// 获取课程类型
    /// </summary>
    /// <returns></returns>
    public DataSet getCourseType()
    {
    	string strsql = "select typeId,typeName from courseType where status = '0'";
    	return db.execSql(strsql);
    }


    /// <summary>
    /// 老师拥有的课程
    /// </summary>
    /// <param name="teacherId"></param>
    /// <returns></returns>
    public DataSet selectCourseByTeacherId(string teacherId)
    {
        string strsql = "select c.CourseID, c.CourseHour,c.about,t.teacherName teacherid,c.gold,ct.typename coursetype,(c.about + '(' + t.teacherName + ')') as ddlAbout,c.courseName from course c,courseType ct,teacher t where c.CourseType=ct.TypeId and c.teacherid =t.teacherid and c.status = '0' and t.status = '0' and c.teacherId = " + teacherId + " order by c.updateTime ";
        return db.execSql(strsql); 
    }


    /// <summary>
    /// 学生购买课程列表
    /// </summary>
    /// <param name="stuId"></param>
    /// <returns>课程编号，课程名称</returns>
    public DataSet selectCourseSellByStuId(string stuId)
    {//

       // string strsql = "select distinct c.courseId,c.courseName,count(distinct c.courseId) as cNum from course c,courseware cw,coursewaredown cwd,[user] u where cwd.coursewareid=cw.coursewareid and cwd.userid=u.userid and cw.courseid=c.courseid and u.userid='" + stuId + "' order by c.courseId";
        string strsql = "select c.courseid ,c.coursename from coursesell cs,course c where cs.courseid=c.courseid and cs.userid='" + stuId + "' order by c.updateTime";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 学生购买课程数量
    /// </summary>
    /// <param name="stuId"></param>
    /// <returns></returns>
    public DataSet selectCourseSellByStuIdCount(string stuId)
    {
        string strsql = "select count(courseid) as cnum from coursesell where userid='" + stuId + "'";
        return db.execSql(strsql);
    }

    

    /// <summary>
    /// 学生已经购买的课搜索
    /// </summary>
    /// <param name="courseId"></param>
    /// <param name="courseType"></param>
    /// <param name="courseName"></param>
    /// <returns></returns>
    public DataSet selectUserCourseSell_search(string userId,string courseId, string courseType, string courseName,string time)
    {
        //and c.status = '0' and t.status = '0'
        string strsql = "select c.courseID,c.courseName,ct.typeName,t.teacherName,c.courseHour,c.gold,cs.[time] from [user] u,coursesell cs,course c ,coursetype ct,teacher t where u.userid=cs.userid  and cs.courseid=c.courseid and t.teacherid=c.teacherid and ct.typeid=c.courseType and u.userid='" + userId + "'";
        strsql += "and c.CourseID like '%" + courseId + "%' ";
        strsql += "and c.CourseName like '%" + courseName + "%' ";
        if (!courseType.Equals(""))
        {
            strsql += "and c.courseType='" + courseType + "' ";
        }
        if (!time.Equals("all"))
        {
            strsql += "and cs.[time] between dateadd(month,-" + time + ",dateadd(day,-1,getdate())) and getdate()";
        }
        strsql += " order by cs.[time] desc ";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 获取全部热门课程
    /// </summary>
    /// <param name="top"></param>
    /// <returns></returns>
    public DataSet selectCourseSellTop(string top)
    {
        string strsql = "select top " + top + " count(cs.courseid) as num,cs.courseid,c.courseName from coursesell cs,course c where c.courseid=cs.courseid group by cs.courseid,c.courseName order by num desc";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 获取分类热门课程
    /// </summary>
    /// <param name="top"></param>
    /// <param name="type"></param>
    /// <returns></returns>
    public DataSet selectCourseSellTopType(string top,string type)
    {
        string strsql = "select top " + top + " count(cs.courseid) as num,cs.courseid,c.courseName from coursesell cs,course c where c.courseid=cs.courseid and courseType='" + type + "' group by cs.courseid,c.courseName order by num desc";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 用户是否购买该课程
    /// </summary>
    /// <param name="stuid"></param>
    /// <param name="courseid"></param>
    /// <returns></returns>
    public bool CourseisSell(string stuid, string courseid)
    {
        string strsql = "select * from coursesell where courseid='" + courseid + "' and userid='" + stuid + "'";
        return db.execSql(strsql).Tables[0].Rows.Count > 0;
    }

    //getdate()

    /// <summary>
    /// 购买课程操作
    /// </summary>
    /// <param name="userid"></param>
    /// <param name="courseid"></param>
    /// <param name="gold"></param>
    /// <returns></returns>
    public bool sellCourse(string userid, string courseid,string gold)
    {
        string[] str = new string[2];
        str[0] = "insert into coursesell(courseid,userid,[time]) values('" + courseid + "','" + userid + "',getdate())";
        str[1] = "update [user] set gold=gold-" + gold + " where userid='" + userid + "'";
        return db.execSqlTran(str);
    }

    /// <summary>
    /// 统计类型购买的次数
    /// </summary>
    /// <param name="type"></param>
    /// <param name="time_1"></param>
    /// <param name="time_2"></param>
    /// <returns></returns>
    public DataSet statisticsSell(string type, string time_1, string time_2)
    {
        string strsql = "select count(cs.id) as num from coursesell cs,course c where cs.courseid=c.courseid and c.coursetype='" + type + "' and cs.[time] between '" + time_1 + "' and DATEADD(day,1,'" + time_2 + "')  group by c.coursetype";
        return db.execSql(strsql);
    }
}